USE [NANS21V]
GO

/****** オブジェクト: SqlProcedure [dbo].[AlltimeRecordDetails] スクリプト日付: 2014/05/19 17:22:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AlltimeRecordDetails]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AlltimeRecordDetails]
GO

CREATE PROCEDURE [dbo].[AlltimeRecordDetails]
	@TAIKAINO int,
	@REKIDAINO int
AS
SELECT 
	LM.LAPMEI AS MEI, 
	dbo.Kiroku(RL.KIROKU,SM.SYUMOKUKUBUN,SM.KIROKUKUBUN,
				(CASE WHEN ISNUMERIC(RL.SOKUTEI)  = 1 then RL.SOKUTEI else 3 END) ) AS KIROKU,
	SM.KIROKUKUBUN, 
	L.ID, 
	RL.SOKUTEI 
FROM 
	TBL_REKIDAI_LAP AS RL, 
	TBL_LAP_MASTER AS LM, 
	TBL_SYUMOKU_MASTER AS SM, 
	TBL_LAP AS L, 
	TBL_REKIDAI AS R, 
	TBL_KYOGI AS K 
WHERE 
	RL.LAPNO = LM.LAPNO 
	AND LM.LAPNO = L.LAPNO 
	AND L.SYUMOKUNO = SM.SYUMOKUNO 
	AND RL.TAIKAINO = @TAIKAINO 
	AND RL.REKIDAINO = @REKIDAINO 
	AND RL.REKIDAINO = R.REKIDAINO 
	AND RL.TAIKAINO = R.TAIKAINO 
	AND R.TAIKAINO = K.TAIKAINO 
	AND R.KYOGINO = K.KYOGINO 
	AND K.SYUMOKUNO = SM.SYUMOKUNO 
	UNION
		SELECT
			SM.SYUMOKUMEI AS MEI, 
			dbo.Kiroku(RK.KIROKU,SM.SYUMOKUKUBUN,SM.KIROKUKUBUN,
				(CASE WHEN ISNUMERIC(RK.SOKUTEI)  = 1 then RK.SOKUTEI else 3 END) ) AS KIROKU,
			SM.KIROKUKUBUN, 
			KM.ID, 
			RK.SOKUTEI 
		FROM 
			TBL_REKIDAI_KONSEI AS RK, 
			TBL_SYUMOKU_MASTER AS SM, 
			TBL_KONSEI_MASTER AS KM, 
			TBL_REKIDAI AS R, 
			TBL_KYOGI AS K 
		WHERE 
			RK.SYUMOKUNO = SM.SYUMOKUNO 
			AND K.SYUMOKUNO = KM.SYUMOKUNO 
			AND KM.KONSEINO = RK.SYUMOKUNO 
			AND RK.TAIKAINO = @TAIKAINO 
			AND RK.REKIDAINO = @REKIDAINO 
			AND RK.REKIDAINO = R.REKIDAINO 
			AND RK.TAIKAINO = R.TAIKAINO 
			AND R.TAIKAINO = K.TAIKAINO 
			AND R.KYOGINO = K.KYOGINO 
		ORDER BY ID

